Quiero establecer el valor predeterminado al obtener datos de la base de datos usando Sequelize ORM. Por ejemplo, tengo una consulta SQL como
SELECT a.affiliate_id, a.active AS current_state, IF(MAX(cn.contract_id) IS NULL ,0, IF(DATEDIFF(NOW(),MAX(cn.contract_date)) > 60,0,1)) AS affiliate_active FROM zn_pf_affiliate a LEFT JOIN zn_clients c ON a.affiliate_id = c.refferal_id AND c.refferal_type = 'affiliate' LEFT JOIN zn_internal_inquiries i ON c.clientid = i.clientID LEFT JOIN zn_pf_contracts cn ON i.inquiry_id = cn.source_object_id AND cn.source_object_type = 'lead' AND cn.stage=3 AND cn.status=3 WHERE a.agency_id = 159297 GROUP BY a.affiliate_id HAVING current_state <> affiliate_active; Quiero convertir esto con Sequelize ORM pero el problema principal es IF(MAX(cn.contract_id) IS NULL ,0, IF(DATEDIFF(NOW(),MAX(cn.contract_date)) > 60,0,1)) AS affiliate_active Cómo escribo esta línea en Sequelize ORM. Gracias
Para usar algunas piezas de SQL en la consulta Sequelize, debe usar Sequelize.literal de esta manera:
const items = Model.findAll({ attributes: [ ...// other attributes, [Sequelize.literal('(IF(MAX(cn.contract_id) IS NULL ,0, IF(DATEDIFF(NOW(),MAX(cn.contract_date)) > 60,0,1)))', 'affiliate_active'] ], ... // other options })